package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.util.Enumeration;
import java.util.Hashtable;

import java.util.Vector;


public class Database {
    
    private String host, user, pass;
    private Connection connection;
    private Statement statement;
    
    public Database( String host, String user, String pass ) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
    {
        this.host = host;
        this.user = user;
        this.pass = pass;
        
        statement = null;
        
        // Decide on the db system directory: <userhome>/.addressbook/
        String userHomeDir = System.getProperty("user.home", ".");
        String systemDir = userHomeDir + "/." + host;

        // Set the db system directory.
        System.setProperty("derby.system.home", systemDir);
        
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        connection = DriverManager.getConnection(getDbUrl()); 
    }
    
    private String getDbUrl()
    {
        return "jdbc:derby:" + host + ";create=true;";//user=" + user + ";password=" + pass;
    }
    
    public void query( String sql ) throws SQLException
    {
        System.out.println( sql );
        Statement stmt = connection.createStatement();
        stmt.execute( sql );
        stmt.close();
    }
    
    public Vector<Hashtable> select( String sql ) throws SQLException
    {
        System.out.println( sql );
        Vector<Hashtable> vector = new Vector<Hashtable>();
        Statement stmt = connection.createStatement();
        ResultSet results = stmt.executeQuery( sql );
        ResultSetMetaData rsmd = results.getMetaData();
        
        int numberCols = rsmd.getColumnCount();
        
        while(results.next())
        {
            System.out.print(".");
            Hashtable hash = new Hashtable();
            for ( int i = 1 ; i <= numberCols ; i++ )
            {
                hash.put( rsmd.getColumnLabel( i ), results.getObject( i ) );  
            }
            vector.add(hash);
        }
        
        stmt.close();
        return vector;
    }
    
    public void close() throws SQLException
    {
        DriverManager.getConnection(getDbUrl() + ";shutdown=true");
        connection.close();
    }
    
    /*public static void main( String args[] ) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
    {
        Database db = new Database( "test", "mariano", "pass" );
        String sqlCreate = "create table termino" +
"(" +
"    id          INTEGER NOT NULL " +
"                PRIMARY KEY GENERATED ALWAYS AS IDENTITY " +
"                (START WITH 1, INCREMENT BY 1)," +
"    palabra     VARCHAR(30), " +
"    significado VARCHAR(255)" +
")";
        try
        {
            db.select( "select 1 from termino" );
            System.out.println( "la base de datos existe" );
        }
        catch( SQLException ex )
        {
            System.out.println( "la base de datos no existe, creando\n" + ex.toString() );
            db.query( sqlCreate );
            
            // lo llenamos con algo (gracias google por las definiciones :D)
            Vector<String[]> items = new Vector<String[]>();
            
            items.add( Query.joinStrings( "palabra", "'auto'" ) );
            items.add( Query.joinStrings( "significado", "'Abreviatura de automóvil'" ) );
            db.query( Query.insert( "termino", items) );
            
            items.clear();
            
            items.add( Query.joinStrings( "palabra", "'casa'" ) );
            items.add( Query.joinStrings( "significado", "'edificación destinada a vivienda'" ) );
            db.query( Query.insert( "termino", items) );
            
            items.clear();
            
            items.add( Query.joinStrings( "palabra", "'perro'" ) );
            items.add( Query.joinStrings( "significado", "'mamíferos que pertenecen a la familia Canidae'" ) );
            db.query( Query.insert( "termino", items) );
        }
        
        Vector<String> tablas = new Vector<String>();
        Vector<String> rows = new Vector<String>();
        
        tablas.add( "termino" );
        rows.add( "palabra" );
        rows.add( "significado" );
        
        Vector<Hashtable> result = db.select( Query.select(tablas, rows, "") );

        for( int i = 0 ; i < result.size() ; i++ )
        {
            Hashtable row = result.get( i );
            
            for (Enumeration<String> e = row.keys (); e.hasMoreElements ();)
            {
                String key = e.nextElement();
                System.out.println( key + ": " + row.get( key ) );
            }
            System.out.println();
        }
    }*/
    
}
